0
确认环境 — USE teaching
准备
▶
SQL
USE teaching;
-- 确认 5 张表都在
SHOW TABLES;
✅ 应看到 c、s、sc、t、tc 五张表。如果缺失请先补建。
Part 04
聚合函数查询
COUNT · SUM · AVG · MAX · MIN — 对一组值进行统计计算
1
例 6-20：SUM 和 AVG — 求总分与平均分
练习
▶
目标：查询学号"s2"的总分和平均分。先不加别名运行看字段名，再加别名。
SQL — 不加别名
SELECT SUM(score), AVG(score)
FROM sc
WHERE sno = 's2';
SQL — 加别名（推荐）
SELECT SUM(score) AS 总分, AVG(score) AS 平均分
FROM sc
WHERE sno = 's2';
✓ 期望结果
| 总分 | 平均分 |
|---|---|
| 225.00 | 75.000000 |
⚠ s2 选了 4 门课，其中 c5 成绩为 NULL。SUM 和 AVG 只计算了 3 门有效成绩，NULL 不参与！平均分 = 225 ÷ 3 = 75，不是 225 ÷ 4。
2
例 6-21：MAX 和 MIN — 最高/最低课时
练习
▶
SQL
SELECT
MAX(ct) AS 最高课时,
MIN(ct) AS 最低课时,
MAX(ct) - MIN(ct) AS 最大课时差
FROM c;
✓ 期望结果
| 最高课时 | 最低课时 | 最大课时差 |
|---|---|---|
| 64 | 32 | 32 |
💡 聚合函数可以参与算术运算，如 MAX(ct) - MIN(ct)。
3
例 6-22：COUNT — 统计选课门数（NULL 陷阱）
练习
▶
目标：分别用 COUNT(cno) 和 COUNT(score) 查询 s2 的选课门数，观察差异。
SQL — COUNT(cno)（正确）
SELECT sno, COUNT(cno) AS 选课门数
FROM sc
WHERE sno = 's2';
SQL — COUNT(score)（有坑！）
-- 试试看，结果和上面一样吗？
SELECT sno, COUNT(score) AS 选课门数
FROM sc
WHERE sno = 's2';
✓ 期望对比
| 写法 | 选课门数 | 说明 |
|---|---|---|
| COUNT(cno) | 4 | ✓ 正确 |
| COUNT(score) | 3 | ✗ 少了 1（NULL 不被计数） |
❗ 核心教训：COUNT(字段名) 只计算非 NULL 值。如果该字段可能有 NULL，用 COUNT(*) 或 COUNT(其他非空字段) 更安全。
4
补充练习：s1 的选课门数（无 NULL 情况）
练习
▶
再查 s1 的选课门数，验证无 NULL 时 COUNT(cno) 和 COUNT(score) 结果一致。
SQL
SELECT sno, COUNT(cno) AS 选课门数
FROM sc
WHERE sno = 's1';
✓ 期望结果
| sno | 选课门数 |
|---|---|
| s1 | 3 |
5
例 6-23：COUNT(DISTINCT) — 统计不重复值
练习
▶
目标：查询学生表中有多少种不同的专业。先不加 DISTINCT 看效果，再加上。
SQL — 不加 DISTINCT
-- 不去重，会把重复专业也数进去
SELECT COUNT(maj) AS 专业数量
FROM s;
SQL — 加 DISTINCT（正确）
SELECT COUNT(DISTINCT maj) AS 专业数量
FROM s;
✓ 期望对比
| 写法 | 专业数量 |
|---|---|
| COUNT(maj) | 8 |
| COUNT(DISTINCT maj) | 4 |
💡 8 名学生只有 4 种专业（计算机、信息、自动化、数学）。DISTINCT 先去重再计数。
6
例 6-24：COUNT(*) — 统计元组数量
练习
▶
SQL
SELECT dept, COUNT(*) AS 教师数量
FROM t
WHERE dept = '信息学院';
✓ 期望结果
| dept | 教师数量 |
|---|---|
| 信息学院 | 5 |
💡 COUNT(*) 统计行数，不看具体字段值，也不跳过 NULL 行。
Part 05
分组查询
GROUP BY 分组 + HAVING 对组筛选 — 理解 WHERE 与 HAVING 的区别
7
例 6-25：GROUP BY — 每门课程的选课人数
练习
▶
SQL
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno;
✓ 期望结果
| 课程号 | 选课人数 |
|---|---|
| c1 | 4 |
| c2 | 3 |
| c3 | 2 |
| c4 | 3 |
| c5 | 1 |
| c6 | 1 |
| c7 | 2 |
💡 GROUP BY cno 把 cno 相同的行归为一组，COUNT(*) 分别统计每组的行数。
8
例 6-26：HAVING — 选修 ≥ 3 门课的学生
练习
▶
目标：先按学号分组统计选课门数，再用 HAVING 筛选出 ≥ 3 门的。
SQL — 第一步：先看所有人的选课门数
-- 先不加 HAVING，看全貌
SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno;
SQL — 第二步：加 HAVING 筛选
SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno
HAVING COUNT(*) >= 3;
✓ 加 HAVING 后的期望结果
| 学号 | 选课门数 |
|---|---|
| s1 | 3 |
| s2 | 4 |
9
深入理解：WHERE 与 HAVING 的区别（实验对比）
思考
▶
WHERE
在分组之前筛选行
不能使用聚合函数
HAVING
在分组之后筛选组
可以使用聚合函数
实验：查询"成绩不为空的选课记录中"，选课 ≥ 2 门的学生。体会 WHERE 在 GROUP BY 前执行。
SQL — WHERE + GROUP BY + HAVING 联用
SELECT sno AS 学号, COUNT(*) AS 有效选课数
FROM sc
WHERE score IS NOT NULL -- ① 先过滤掉 NULL 行
GROUP BY sno -- ② 再按学号分组
HAVING COUNT(*) >= 2; -- ③ 最后筛选组
🔑 执行顺序：WHERE → GROUP BY → HAVING。WHERE 先排除了 NULL 成绩的行，然后才分组和过滤。
Part 06
查询结果排序
ORDER BY 子句 — ASC 升序（默认）· DESC 降序 · 多字段排序
10
例 6-27：单字段降序 — 按成绩排序
练习
▶
SQL
SELECT sno, cno, score
FROM sc
WHERE sno = 's2'
ORDER BY score DESC;
✓ 期望结果（注意 NULL 排在最后或最前取决于实现）
| sno | cno | score |
|---|---|---|
| s2 | c2 | 82.00 |
| s2 | c1 | 78.00 |
| s2 | c7 | 65.00 |
| s2 | c5 | NULL |
💡 MySQL 中 DESC 排序时，NULL 被视为最小值，排在最后。
11
例 6-28：观察默认排序规则
练习
▶
目标：按课时降序查课程表，观察课时相同时 MySQL 默认怎么排。
SQL
SELECT *
FROM c
ORDER BY ct DESC;
✓ 期望结果（注意 ct=48 和 ct=32 的行的排列）
| cno | cn | ct |
|---|---|---|
| c4 | 数据结构 | 64 |
| c7 | 高等数学 | 60 |
| c5 | 数据库系统 | 56 |
| c2 | 程序设计基础 | 48 |
| c3 | 线性代数 | 48 |
| c1 | Java程序设计 | 40 |
| c6 | 数据挖掘 | 32 |
| c8 | 控制理论 | 32 |
💡 ct=48 的两门课按 cno 升序排列（c2 在 c3 前），ct=32 同理。这是 MySQL 默认按主键升序补充排序。
12
例 6-29：多字段排序 — 主排序 + 次排序
练习
▶
目标：课时降序 → 课时相同按课程名降序。对比上一步结果的差异。
SQL
SELECT *
FROM c
ORDER BY ct DESC, cn DESC;
✓ 期望结果（注意 ct=48 时 c3 线性代数 在 c2 前）
| cno | cn | ct |
|---|---|---|
| c4 | 数据结构 | 64 |
| c7 | 高等数学 | 60 |
| c5 | 数据库系统 | 56 |
| c3 | 线性代数 | 48 |
| c2 | 程序设计基础 | 48 |
| c1 | Java程序设计 | 40 |
| c8 | 控制理论 | 32 |
| c6 | 数据挖掘 | 32 |
⚠ 对比上一步：ct=48 时，上一步 c2 在 c3 前（默认按主键），这一步 c3 在 c2 前（按课程名 cn 降序排列，"线" > "程" 字典序）。
Part 07
限制查询结果数量（LIMIT）
LIMIT 子句限制返回行数，OFFSET 指定起始偏移 — 分页的基础
13
例 6-30：LIMIT + OFFSET — 两种等价写法
练习
▶
目标：从第 2 位教师开始取 3 位。分别用两种语法，验证结果相同。
SQL — 写法一：LIMIT offset, count
SELECT tno, tn, prof
FROM t
LIMIT 1, 3;
SQL — 写法二：LIMIT count OFFSET offset
SELECT tno, tn, prof
FROM t
LIMIT 3 OFFSET 1;
✓ 期望结果（两种写法完全相同）
| tno | tn | prof |
|---|---|---|
| t2 | 周建 | 讲师 |
| t3 | 顾伟 | 副教授 |
| t4 | 赵礼 | 教授 |
💡
LIMIT 1, 3 — 第一个数字 1 是偏移量（跳过 1 行），第二个数字 3 是取几行。OFFSET 从 0 开始计数。14
拓展实验：模拟分页（每页 3 条）
思考
▶
场景：课程表 8 行，每页显示 3 行。分别查第 1、2、3 页。
SQL — 第 1 页
SELECT * FROM c LIMIT 3 OFFSET 0; -- 第1~3行
SQL — 第 2 页
SELECT * FROM c LIMIT 3 OFFSET 3; -- 第4~6行
SQL — 第 3 页
SELECT * FROM c LIMIT 3 OFFSET 6; -- 第7~8行（只剩2行）
🔑 分页公式：OFFSET = (页码 - 1) × 每页行数。第 3 页只返回 2 行，因为总共只有 8 行。
15
例 6-31：综合应用 — GROUP BY + ORDER BY + LIMIT
练习
▶
目标：查询每门课的选课人数，按人数降序，只显示前 3 名。
SQL
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno
ORDER BY 选课人数 DESC
LIMIT 3;
✓ 期望结果（选课人数最多的 3 门课）
| 课程号 | 选课人数 |
|---|---|
| c1 | 4 |
| c2 | 3 |
| c4 | 3 |
✅ 这是一个典型的"Top N"查询模式：GROUP BY 分组 → ORDER BY 排序 → LIMIT 取前 N。在实际工作中非常常用！
自测挑战
独立完成 — 先写再看答案
综合运用聚合函数、GROUP BY、ORDER BY、LIMIT
🏋️ 挑战 1：查询选课表 sc 中的最高分、最低分和平均分。
参考答案
SELECT
MAX(score) AS 最高分,
MIN(score) AS 最低分,
AVG(score) AS 平均分
FROM sc;
🏋️ 挑战 2：查询每个学生的平均成绩，按平均成绩降序排列。（提示：GROUP BY + ORDER BY）
参考答案
SELECT sno, AVG(score) AS 平均成绩
FROM sc
GROUP BY sno
ORDER BY 平均成绩 DESC;
🏋️ 挑战 3：查询平均成绩 ≥ 80 分的学生学号和平均成绩。（提示：HAVING）
参考答案
SELECT sno, AVG(score) AS 平均成绩
FROM sc
GROUP BY sno
HAVING AVG(score) >= 80;
🏋️ 挑战 4：查询每个学院的教师人数，只显示教师人数 ≥ 2 的学院，结果按人数降序。
参考答案
SELECT dept AS 学院, COUNT(*) AS 教师人数
FROM t
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY 教师人数 DESC;
🏋️ 挑战 5：查询选课人数最多的那一门课的课程号和选课人数。（提示：ORDER BY + LIMIT 1）
参考答案
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno
ORDER BY 选课人数 DESC
LIMIT 1;
🏋️ 挑战 6：查询所有学生的选课信息（学号、课程号、成绩），按学号升序排列，学号相同再按成绩降序，只显示前 5 条。
参考答案
SELECT sno, cno, score
FROM sc
ORDER BY sno ASC, score DESC
LIMIT 5;
🏋️ 挑战 7（综合）：查询"有成绩的选课记录中"，每个学生的总分，只显示总分排名前 3 的学生学号和总分。
参考答案
SELECT sno, SUM(score) AS 总分
FROM sc
WHERE score IS NOT NULL
GROUP BY sno
ORDER BY 总分 DESC
LIMIT 3;
🔑 本题同时用到了 WHERE + GROUP BY + ORDER BY + LIMIT，是 SELECT 语句所有子句的综合运用。子句顺序不可颠倒！